import db from "../db/db.js";

const paramsSearch = async (params: any) => {
    return new Promise((resolve, reject) => {
        let sql = `SELECT * FROM consumptionRecord`;
        const queryArr: any = [];
        const result = detailSql(params, sql, queryArr);
        sql = result.sql;
        queryArr.push(...result.queryArr);

        // WHERE
        // counterParty LIKE CONCAT('%', ?, '%')
        // amount BETWEEN ? AND ?
        // AND tradingTime>= '2024-08-02 14:13:11' AND tradingTime<= '2024-08-06 19:46:29'

        db.query(sql, queryArr, (err, results) => {
            if (err) {
                return reject(err);
            }
            resolve(results);
        });
    });
};

const detailSql = (params,sql,queryArr) => {
    const { transactionObject, transactionType, description, remake, minNum, maxNum, startTime, endTime,ignoreFlag } = params;
    let isFirst = true;
    let paramsNum = 0;

    if (startTime && endTime) {
        const result = judgeSql(isFirst, paramsNum, sql);
        isFirst = result.isFirst;
        paramsNum = result.paramsNum;
        sql = result.sql;
        sql += `  tradingTime BETWEEN ? AND ?`;
        let startTimeTrans = timestampToDateString(startTime);
        let endTimeTrans = timestampToDateString(endTime);
        queryArr.push(startTimeTrans, endTimeTrans);
    }
    if (transactionType) {
        const result = judgeSql(isFirst, paramsNum, sql);
        isFirst = result.isFirst;
        paramsNum = result.paramsNum;
        sql = result.sql;
        sql += ` tradingType LIKE ?`;
        queryArr.push(transactionType);
    }
    if (transactionObject) {
        const result = judgeSql(isFirst, paramsNum, sql);
        isFirst = result.isFirst;
        paramsNum = result.paramsNum;
        sql = result.sql;
        sql += ` counterParty LIKE CONCAT('%', ?, '%')`;
        queryArr.push(transactionObject);
    }
    if (description) {
        const result = judgeSql(isFirst, paramsNum, sql);
        isFirst = result.isFirst;
        paramsNum = result.paramsNum;
        sql = result.sql;
        sql += ` tradeDescription LIKE CONCAT('%', ?, '%')`;
        queryArr.push(description);
    }
    if (minNum || maxNum) {
        const result = judgeSql(isFirst, paramsNum, sql);
        isFirst = result.isFirst;
        paramsNum = result.paramsNum;
        sql = result.sql;
        if (minNum && maxNum) {
            sql += ` amount BETWEEN ? AND ?`;
            queryArr.push(minNum, maxNum);
        }
        if (minNum && !maxNum) {
            sql += ` amount >= ?`;
            queryArr.push(minNum);
        }
        if (!minNum && maxNum) {
            sql += ` amount <= ?`;
            queryArr.push(maxNum);
        }
    }
    if (remake) {
        const result = judgeSql(isFirst, paramsNum, sql);
        isFirst = result.isFirst;
        paramsNum = result.paramsNum;
        sql = result.sql;
        sql += ` remake LIKE CONCAT('%', ?, '%')`;
        queryArr.push(remake);
    }
    if (ignoreFlag) {
        const result = judgeSql(isFirst, paramsNum, sql);
        isFirst = result.isFirst;
        paramsNum = result.paramsNum;
        sql = result.sql;
        sql += ` ignoreFlag = ?`;
        queryArr.push(0);
    }
    return { sql, queryArr };
};

const judgeSql = (isFirst: boolean, paramsNum: number, sql: string) => {
    isFirst ? (sql += ` WHERE`) : null;
    isFirst = false;
    paramsNum++;
    paramsNum > 1 ? (sql += ` AND`) : null;
    return { isFirst, paramsNum, sql };
};

const timestampToDateString = (timestamp: number | string): string => {
    timestamp = Number(timestamp);
    // 创建一个 Date 对象
    const date = new Date(timestamp);

    // 格式化日期和时间
    const year = date.getFullYear();
    const month = String(date.getMonth() + 1).padStart(2, "0"); // 月份是 0-11，所以要加 1
    const day = String(date.getDate()).padStart(2, "0");
    const hours = String(date.getHours()).padStart(2, "0");
    const minutes = String(date.getMinutes()).padStart(2, "0");
    const seconds = String(date.getSeconds()).padStart(2, "0");

    // 返回格式化后的日期时间字符串
    return `${year}-${month}-${day} ${hours}:${minutes}:${seconds}`;
};

export default paramsSearch;
